package com.FH.util;

import javax.annotation.Resource;
import javax.ejb.EJBException;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class AccessDB {

	@Resource(mappedName="java:/jyylDS")
	DataSource hrDs;
	
	//输入sql获取返回列表
	public List<String []> getReportingTo(String sql) {
		Connection conn = null;
		PreparedStatement sth = null;
		List<String []> list = new ArrayList<String []>();
		try {
			conn = hrDs.getConnection();
			sth = conn.prepareStatement(sql);
			ResultSet rs = sth.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			rsmd.getColumnCount();
			while (rs.next()) {
				String [] row = new String[rsmd.getColumnCount()+1];
				for (int i = 1; i < rsmd.getColumnCount()+1; i++) {
					row[i] = rs.getString(i)==null ? "" : rs.getString(i);
				}
				list.add(row);
			}
		} catch (SQLException e) {
			e.getMessage();
		}finally {
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {
					System.out.println(e.getMessage());
				}
		}
		return list;
	}

	/**
	 * 执行update与insert语句方法
	 * @param sql
	 * @return 受影响行数
	 * @author syf
	 */
	public int createReportingTo(String sql) {
		Connection conn = null;
		Statement statement = null;
		int result = 0;
		try {
			conn = hrDs.getConnection();
			statement = conn.createStatement();
			int rs = statement.executeUpdate(sql);
			result = rs;
		} catch (SQLException e) {
			throw new EJBException(e.getMessage());
		}finally {
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {
					System.out.println(e.getMessage());
				}
		}
		return result;
	}
	
	
	/**
	 * 执行update与insert语句方法
	 * @param sql
	 * @return 受影响行数
	 * @author syf
	 */
	public int addReportingTo(String sql) {
		Connection conn = null;
		PreparedStatement sth = null;
		int result = 0;
		try {
			conn = hrDs.getConnection();
			sth = conn.prepareStatement(sql);
			int rs = sth.executeUpdate();
			result = rs;
		} catch (SQLException e) {
			throw new EJBException(e.getMessage());
		}finally {
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {
					System.out.println(e.getMessage());
				}
		}
		return result;
	}

	
	/**
	 * 查询单一数据sql
	 * @param sql
	 * @return
	 */
	public String getReportingToOne(String sql) {
		Connection conn = null;
		PreparedStatement sth = null;
		String result = "";
		try {
			conn = hrDs.getConnection();
			sth = conn.prepareStatement(sql);
			ResultSet rs = sth.executeQuery();
			System.out.println();
			if(rs.next())
			{
				result = rs.getString(1);
			}else
			{
				result= "";
			}
		} catch (SQLException e) {
			throw new EJBException(e);
		}
		finally {
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {
					System.out.println(e.getMessage());
				}
		}
		return result;
	}
	
	
	/**
	 * 查询配置图片地址(每个项目不同需要修改sql后边的参数项目版本号)
	 * @return String[]
	 */
	public String[] getImage_path() {
		String image_path_sql= "SELECT i.`image_path_addr`,i.`image_path_serveraddress` FROM image_path i WHERE i.`jy_version` = '1-1'";
		List<String []> seresult = new ArrayList<String []>();
		seresult = this.getReportingTo(image_path_sql);
		return seresult.get(0);
	}
}
